﻿using Entity;
using Entity.SysEntity;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DBService.Handler.Base
{
    public class SqlHelper
    {
        #region 创建参数数据集
        /// <summary>
        /// 创建参数数据集
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="start">开始</param>
        /// <returns></returns>
        public virtual Hashtable CreateParameterString(List<EntityStructure> structList, string number)
        {
            StringBuilder sb = new StringBuilder();

            List<SqlParameter> parList = new List<SqlParameter>();//参数集合
            if (number == null)
            {
                number = "";
            }
            SqlParameter par;
            foreach (EntityStructure st in structList)
            {
                if (st.Types.Equals(QueryType.Like))
                {
                    par = new SqlParameter();
                    par.ParameterName = "@" + st.FileName + number;
                    par.Value = "%" + st.FileValue + "%";
                    parList.Add(par);
                }
                else
                {
                    par = new SqlParameter();
                    par.ParameterName = "@" + st.FileName + number;
                    par.Value = st.FileValue;
                    parList.Add(par);
                }
                if (sb.Length > 0)
                {
                    sb.Append(" and ");
                }
                sb.Append(st.FileName);
                if (st.Types.Equals(QueryType.Equal))
                { //等于
                    sb.Append("=");
                }
                else if (st.Types.Equals(QueryType.NotEqual))
                {//不等于
                    sb.Append("!=");
                }
                else if (st.Types.Equals(QueryType.Like))
                {//模糊查询
                    if (st.FileType.Trim().ToUpper().IndexOf("INT") > -1)
                    {//数值字段只能使用精确查询
                        sb.Append("=");
                    }
                    else
                    {//非数值
                        sb.Append(" like ");
                    }
                }
                else if (st.Types.Equals(QueryType.LessThan))
                {//小于
                    if (st.FileType.Trim().ToUpper().IndexOf("INT") > -1)
                    {//数值
                        sb.Append("< ");
                    }
                    else
                    {//非数值
                        sb.Append("= ");
                    }
                }
                else if (st.Types.Equals(QueryType.LessThan))
                {//大于
                    if (st.FileType.Trim().ToUpper().IndexOf("INT") > -1)
                    {//数值
                        sb.Append("> ");
                    }
                    else
                    {//非数值
                        sb.Append("= ");
                    }
                }
                sb.Append("@" + st.FileName + number);
            }
            Hashtable tables = new Hashtable();
            tables.Add("parameter", parList);
            tables.Add("whereString", sb.ToString());
            return tables;
        }
        #endregion

        #region Insert语句拼接字符
        /// <summary>
        /// Insert语句拼接字符
        /// </summary>
        /// <param name="data">数据对象</param>
        /// <param name="number">索引值</param>
        /// <returns></returns>
        public virtual Hashtable AddCreate(object data, string number)
        {
            List<string> list = new List<string>();
            StringBuilder columns = new StringBuilder();//操作列
            StringBuilder values = new StringBuilder();//value
            List<SqlParameter> parList = new List<SqlParameter>();//参数集合
            Type type = data.GetType();
            PropertyInfo[] pi = type.GetProperties();
            if (number == null)
            {
                number = "";
            }
            SqlParameter par;
            foreach (PropertyInfo p in pi)
            {
                var value = p.GetValue(data, null);
                if (value != null)
                {
                    columns.Append(p.Name + ",");
                    values.Append("@" + p.Name + number + ",");
                    par = new SqlParameter();
                    par.ParameterName = "@" + p.Name + number;
                    if (value.ToString() == "-" || value.ToString() == "—")
                    {
                        par.Value = "";
                    }
                    else
                    {
                         if (p.PropertyType == typeof(string))
                        {
                            value = value.ToString().Trim();
                        }
                        par.Value = value;
                    }

                    parList.Add(par);
                }
            }
            Hashtable tables = new Hashtable();
            tables.Add("columns", columns.ToString().Substring(0, columns.Length - 1));
            tables.Add("values", values.ToString().Substring(0, values.Length - 1));
            tables.Add("parameter", parList);
            return tables;
        }
        #endregion
        /// <summary>
        /// 录入数据时拼接sql方法
        /// </summary>
        /// <param name="data">参数集合(键值对格式)</param>
        /// <param name="number">当前序号</param>
        /// <returns></returns>
        public virtual Hashtable CreateAddColumnsByDictionary(Dictionary<string, object> data, string number)
        {
            List<string> list = new List<string>();
            StringBuilder columns = new StringBuilder();//操作列
            StringBuilder values = new StringBuilder();//value
            List<SqlParameter> parList = new List<SqlParameter>();//参数集合
            if (number == null)
            {
                number = "";
            }
            SqlParameter par;
            foreach (KeyValuePair<string, object> k in data)
            {
                columns.Append(k.Key.ToString().Trim() + ",");
                values.Append("@" + k.Key.ToString().Trim() + number + ",");
                par = new SqlParameter();
                par.ParameterName = "@" + k.Key.ToString().Trim() + number;
                par.Value = k.Value;
                parList.Add(par);
            }
            Hashtable tables = new Hashtable();
            tables.Add("columns", columns.ToString().Substring(0, columns.Length - 1));
            tables.Add("values", values.ToString().Substring(0, values.Length - 1));
            tables.Add("parameter", parList);
            return tables;
        }
        /// <summary>
        /// 编辑数据时拼接sql生成方法
        /// </summary>
        /// <param name="dic">参数集合(键值对格式)</param>
        /// <param name="conditionStr">条件语句</param>
        /// <param name="number">序号</param>
        /// <returns></returns>
        public virtual Hashtable CreateEditColumnsByDictionary(Dictionary<string, object> dic, string conditionStr, string number)
        {
            Hashtable tables = new Hashtable();
            var conArr = conditionStr.Split(',');
            StringBuilder setString = new StringBuilder();//修改字段语句
            StringBuilder whereString = new StringBuilder();//where 条件语句
            List<SqlParameter> parList = new List<SqlParameter>();//参数集合
            if (number == null)
            {
                number = "";
            }
            bool isCondition = false;
            int conditionCount = 0;//条件计数(作用优化程序运行速度)
            SqlParameter par;
            foreach (KeyValuePair<string, object> k in dic)
            {
                isCondition = false;
                par = new SqlParameter();
                if (conditionCount < conArr.Length)
                {
                    for (int i = 0; i < conArr.Length; i++)
                    {
                        if (k.Key.Trim().ToUpper().Equals(conArr[i].Trim().ToUpper()))
                        {
                            isCondition = true;
                            conditionCount++;
                        }
                    }
                }
                if (isCondition)
                {
                    #region 字段为条件字段
                    if (whereString.Length > 0)
                    {
                        whereString.Append(" and ");
                    }
                    whereString.Append(k.Key.Trim() + "=");
                    whereString.Append("@" + k.Key.Trim() + number);
                    #endregion
                }
                else
                {
                    #region 非条件字段
                    setString.Append(k.Key.Trim() + "= @" + k.Key.Trim() + number + " ,");
                    #endregion
                }
                par.ParameterName = "@" + k.Key.Trim() + number;
                par.Value = k.Value;
                parList.Add(par);

            }

            tables.Add("set", setString.ToString().Substring(0, setString.Length - 1));
            tables.Add("where", whereString.ToString());
            tables.Add("parameter", parList);
            return tables;
        }



        /// <summary>
        /// 删除数据时拼接sql生成方法
        /// </summary>
        /// <param name="dic">参数集合(键值对格式)</param>
        /// <param name="number">序号</param>
        /// <returns></returns>
        public virtual Hashtable CreateDelColumnsByDictionary(Dictionary<string, object> dic, string number)
        {
            Hashtable tables = new Hashtable();
            StringBuilder whereString = new StringBuilder();//where 条件语句
            List<SqlParameter> parList = new List<SqlParameter>();//参数集合
            if (number == null)
            {
                number = "";
            }
            SqlParameter par;
            foreach (KeyValuePair<string, object> k in dic)
            {
                par = new SqlParameter();
                #region 字段为条件字段
                if (whereString.Length > 0)
                {
                    whereString.Append(" and ");
                }
                whereString.Append(k.Key.Trim() + "=");
                whereString.Append("@" + k.Key.Trim() + number);
                #endregion
                par.ParameterName = "@" + k.Key.Trim() + number;
                par.Value = k.Value;
                parList.Add(par);

            }
            tables.Add("where", whereString.ToString());
            tables.Add("parameter", parList);
            return tables;
        }


        #region
        public virtual Hashtable EditCreate(List<EntityStructure> structList, string conditionStr, string number)
        {
            Hashtable tables = new Hashtable();
            var conArr = conditionStr.Split(',');
            StringBuilder setString = new StringBuilder();//修改字段语句
            StringBuilder whereString = new StringBuilder();//where 条件语句
            List<SqlParameter> parList = new List<SqlParameter>();//参数集合
            if (number == null)
            {
                number = "";
            }
            bool isCondition = false;
            int conditionCount = 0;//条件计数(作用优化程序运行速度)
            SqlParameter par;
            foreach (EntityStructure strut in structList)
            {
                isCondition = false;
                par = new SqlParameter();
                if (conditionCount < conArr.Length)
                {
                    for (int i = 0; i < conArr.Length; i++)
                    {
                        if (strut.FileName.Trim().ToUpper().Equals(conArr[i].Trim().ToUpper()))
                        {
                            isCondition = true;
                            conditionCount++;
                        }
                    }
                }
                if (isCondition)
                {
                    #region 字段为条件字段
                    if (whereString.Length > 0)
                    {
                        whereString.Append(" and ");
                    }
                    whereString.Append(strut.FileName.Trim());
                    #region 条件检索类型
                    if (strut.Types.Equals(QueryType.Equal))
                    { //等于
                        whereString.Append("=");
                    }
                    else if (strut.Types.Equals(QueryType.NotEqual))
                    {//不等于
                        whereString.Append("!=");
                    }
                    else if (strut.Types.Equals(QueryType.Like))
                    {//模糊查询
                        if (strut.FileType.Trim().ToUpper().IndexOf("INT") > -1)
                        {//数值字段只能使用精确查询
                            whereString.Append("=");
                        }
                        else
                        {//非数值
                            whereString.Append(" like ");
                        }
                    }
                    else if (strut.Types.Equals(QueryType.LessThan))
                    {//小于
                        if (strut.FileType.Trim().ToUpper().IndexOf("INT") > -1)
                        {//数值
                            whereString.Append("< ");
                        }
                        else
                        {//非数值
                            whereString.Append("= ");
                        }
                    }
                    else if (strut.Types.Equals(QueryType.LessThan))
                    {//大于
                        if (strut.FileType.Trim().ToUpper().IndexOf("INT") > -1)
                        {//数值
                            whereString.Append("> ");
                        }
                        else
                        {//非数值
                            whereString.Append("= ");
                        }
                    }
                    #endregion
                    whereString.Append("@" + strut.FileName.Trim() + number);
                    #endregion
                }
                else
                {
                    #region 非条件字段
                    setString.Append(strut.FileName.Trim() + "= @" + strut.FileName.Trim() + number + " ,");
                    #endregion
                }
                par.ParameterName = "@" + strut.FileName.Trim() + number;
                var value = strut.FileValue;
                if (value.ToString() == "-" || value.ToString() == "—")
                {
                    par.Value = "";
                }
                else
                {
                    par.Value = strut.FileValue;
                }
                parList.Add(par);

            }

            tables.Add("set", setString.ToString().Substring(0, setString.Length - 1));
            tables.Add("where", whereString.ToString());
            tables.Add("parameter", parList);
            return tables;
        }
        #endregion



    }
}
